package com.ibm.ems.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ibm.ems.dto.EventBean;
import com.ibm.ems.dto.LocationBean;
import com.ibm.ems.dto.PromotionBean;

public class EventsDAO {

	private Connection conn;

	public EventsDAO(Connection conn) {
		this.conn = conn;
	}

	public void CreateEvent(EventBean e) throws SQLException {
		PreparedStatement pstmt = null;

		try {

			pstmt = conn.prepareStatement("Insert into events values (?,?,?,?,?,?,?,?,?)");
			pstmt.setString(1, e.getEventId());
			pstmt.setString(2, e.getEventName());
			pstmt.setDate(3, new java.sql.Date(e.getStartDate().getTime()));
			pstmt.setDate(4, new java.sql.Date(e.getEndDate().getTime()));
			pstmt.setInt(5, e.getClassSize());
			pstmt.setInt(6, e.getFees());
			pstmt.setString(8, e.getUserName());
			pstmt.setString(7, e.getLocationBean().getLocationId());
			pstmt.setString(9, e.getPromotionBean().getProId());

			pstmt.executeQuery();

		} catch (SQLException ex) {
			ex.printStackTrace();

		}
	}

	public void ModifyEvtdb(EventBean s) {
		PreparedStatement pstnt = null;

		try {
			pstnt = conn.prepareStatement("Update table events set event_name=?,start_date=?,end_date=?,class_size=?,fess=?,location_id=?,speaker_name=?,promotion_id=? where event_id=?");

			pstnt.setString(1, s.getEventName());
			pstnt.setDate(2, new java.sql.Date(s.getStartDate().getTime()));
			pstnt.setDate(3, new java.sql.Date(s.getEndDate().getTime()));
			pstnt.setInt(4, s.getClassSize());
			pstnt.setInt(5, s.getFees());
			pstnt.setString(6, s.getLocationBean().getLocationId());
			pstnt.setString(7, s.getUserName());
			pstnt.setString(8, s.getPromotionBean().getProId());

			pstnt.setString(9, s.getEventId());

			pstnt.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void EventsDaoInit(ArrayList<EventBean> l) {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement("select * from Events e left join Location l on e.location_id = l.location_id");
			int count = 0;
			EventBean f = new EventBean();
			rs = pstmt.executeQuery();

			while (rs.next()) {
				f.setEventId(rs.getString("Event_Id"));
				f.setEventName(rs.getString("Event_Name"));
				System.out.println(rs.getDate("Start_Date"));
				System.out.println(rs.getDate("Start_Date").getTime());
				f.setStartDate(new java.util.Date(rs.getDate("Start_Date")
						.getTime()));
				f.setEndDate(new java.util.Date(rs.getDate("End_Date")
						.getTime()));
				f.setClassSize(rs.getInt("Class_Size"));
				f.setFees(rs.getInt("Fees"));
				LocationBean location = new LocationBean();
				location.setLocationId(rs.getString("Location_Id"));
				location.setLocationDesc(rs.getString("Location_Desc"));
				f.setLocationBean(location);

				f.setUserName(rs.getString("Speaker_Name"));

				PromotionBean promotion = new PromotionBean();
				promotion.setProId(rs.getString("Promotion_Id"));
				f.setPromotionBean(promotion);

				l.add(count, f);

				f = new EventBean();
				count++;
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
			} catch (Exception ex1) {

			}
		}

	}
	//To Recieve data from database
	public void modify(EventBean f)
	{
		
		PreparedStatement pstmt = null;
		ResultSet rs;
	try{
		pstmt=conn.prepareStatement("select event_name,start_date,end_date,class_size,fess,location_id,speaker_name,promotion_id where event_id=?");
		pstmt.setString(1,f.getEventId());
		
		rs = pstmt.executeQuery();
		
		rs.next();
		f.setEventName(rs.getString("Event_Name"));
		System.out.println(rs.getDate("Start_Date"));
		System.out.println(rs.getDate("Start_Date").getTime());
		f.setStartDate(new java.util.Date(rs.getDate("Start_Date")
				.getTime()));
		f.setEndDate(new java.util.Date(rs.getDate("End_Date")
				.getTime()));
		f.setClassSize(rs.getInt("Class_Size"));
		f.setFees(rs.getInt("Fees"));
		LocationBean location = new LocationBean();
		location.setLocationId(rs.getString("Location_Id"));
		f.setLocationBean(location);

		f.setUserName(rs.getString("Speaker_Name"));

		PromotionBean promotion = new PromotionBean();
		promotion.setProId(rs.getString("Promotion_Id"));
		f.setPromotionBean(promotion);

		
		
		
			
		
	}catch(SQLException e)
	{}
		
		


	}
	//--------------------------------------------------------------------------------
	//--------------------------------------------------------------------------------
	//To modify in database
	public void modifydatabase(EventBean s)
	{     
		PreparedStatement pstnt = null;

		try{
			pstnt=conn.prepareStatement("Update events set event_name=?,start_date=?,end_date=?,class_size=?,fees=?,location_id=?,speaker_name=?,promotion_id=? where event_id=?");
			System.out.println("hi coming");
			pstnt.setString(1, s.getEventName());
			pstnt.setDate(2, new java.sql.Date(s.getStartDate().getTime()));
			pstnt.setDate(3, new java.sql.Date(s.getEndDate().getTime()));
			pstnt.setInt(4, s.getClassSize());

			pstnt.setInt(5, s.getFees());
			pstnt.setString(6, s.getLocationBean().getLocationId());
			
			pstnt.setString(7, s.getUserName());
			pstnt.setString(8, s.getPromotionBean().getProId());

			pstnt.setString(9, s.getEventId());
			
			System.out.println("hi modifying1123");
			pstnt.executeQuery();
			
			System.out.println("hi modifying1123");	
		
		}catch(SQLException e)
		{
			}
		}

	}

